﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Registered_Student_Management
{
    public partial class F_NewStudent : Form
    {
        string connection_string = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=Database.mdb;Jet OLEDB:Database Password=51002201";
        public F_NewStudent()
        {
            InitializeComponent();
        }
        public int idclass;
        public int getIDclass(string a)
        {
            idclass = int.Parse(a.ToString());
            return idclass;
        }

        public string nameclass;
        public string getNAMEclass(string b)
        {
            nameclass = b;
            return nameclass;
        }
        
        public int idcourse;
        public int getIDcourse(string c)
        {
            idcourse = int.Parse(c.ToString());
            return idcourse;
        }

       
        private void Form2_Load(object sender, EventArgs e)
        {
            GB_NewStudent.Visible = true;
            DTP_ngaytaoHVadd.Value = DateTime.Now;  
        }

        private void GB_NewStudent_VisibleChanged(object sender, EventArgs e)
        {
            // load data to combobox
            string[] s = { "Nam", "Nữ" };

            this.CbB_gioitinhHVadd.DataSource = s;

        }

        private void BT_Add_Click(object sender, EventArgs e)
        {
            try
            {
                if (TB_nghenghiepHVadd.Text == "" || DTP_ngaysinhHVadd.Text == "" || CbB_gioitinhHVadd.Text == "" || TB_tenHVadd.Text == "" || TB_diachiHVadd.Text == "" || TB_emailHVadd.Text == "" || TB_sdtHVadd.Text == "" || TB_mabienlaiHVadd.Text == "" || DTP_ngaytaoHVadd.Text == "" || TB_nguoitaoHVadd.Text == "" || TB_tiendadongHVadd.Text == "")
                {
                    MessageBox.Show("Nhập Đầy Đủ Thông Tin", "Yêu Cầu");
                }
                else
                {
                    OleDbConnection cnInsert = new OleDbConnection();
                    cnInsert.ConnectionString = connection_string;
                    
                                      
                    string birthday = DTP_ngaysinhHVadd.Value.ToShortDateString();

                    string icode = TB_mabienlaiHVadd.Text, create_date = DTP_ngaytaoHVadd.Value.ToShortDateString(), create_person = TB_nguoitaoHVadd.Text;
                    int deposit = int.Parse(TB_tiendadongHVadd.Text.ToString());

                    string fullname = TB_tenHVadd.Text, gender = CbB_gioitinhHVadd.Text, career = TB_nghenghiepHVadd.Text, phone = TB_sdtHVadd.Text, address = TB_diachiHVadd.Text, email = TB_emailHVadd.Text;

                    OleDbCommand commandInsertStu = new OleDbCommand();
                    commandInsertStu.Connection = cnInsert;

                    OleDbCommand commandInsertJob = new OleDbCommand();
                    commandInsertJob.Connection = cnInsert;

                    OleDbCommand commandInsertInvoice = new OleDbCommand();
                    commandInsertInvoice.Connection = cnInsert;

                    //insert new student to database
                    cnInsert.Open();

                    commandInsertStu.CommandText = "insert into student (fullname ,gender,birthday,career,phone,address,email) values('" + fullname + "','" + gender + "','" + birthday + "','" + career + "','" + phone + "','" + address + "','" + email + "')";
                    commandInsertStu.ExecuteNonQuery();

                    OleDbCommand commandgetID = new OleDbCommand();
                    commandgetID.Connection = cnInsert;
                    commandgetID.CommandText = "select * from student where fullname = @fullname";
                    commandgetID.Parameters.AddWithValue("@fullname", TB_tenHVadd.Text);
                    OleDbDataAdapter adapt = new OleDbDataAdapter(commandgetID);
                    DataTable data = new DataTable();
                    adapt.Fill(data);
                    DataRow dr = data.Rows[0];
                    int idHV = int.Parse(dr["id"].ToString());

                    commandInsertJob.CommandText = "insert into job (id_student,name_student,id_class,name_class,create_date,create_person) values('" + idHV + "','" + fullname + "','" + idclass + "','" + nameclass + "','" + create_date + "','" + create_person + "')";
                    commandInsertJob.ExecuteNonQuery();

                    // get tuition of invoice
                    OleDbCommand commandgetTuition = new OleDbCommand();
                    commandgetTuition.Connection = cnInsert;
                    commandgetTuition.CommandText = "select * from course where id = @id ";
                    commandgetTuition.Parameters.AddWithValue("@id", idcourse);
                    OleDbDataAdapter adapt1 = new OleDbDataAdapter(commandgetTuition);
                    DataTable data1 = new DataTable();
                    adapt1.Fill(data1);
                    DataRow dr1 = data1.Rows[0];
                    int tuited = int.Parse(dr1["tuition"].ToString());
                    int arrears = tuited - deposit;
                    int waiting_student = int.Parse(dr1["waiting_student"].ToString());
                    

                    commandInsertInvoice.CommandText = "insert into invoice(belong, create_date, create_person, icode, tuition, deposit, arrears) values('" + idHV + "','" + create_date + "','" + create_person + "','" + icode + "','" + tuited + "','" + deposit + "','" + arrears + "')";
                    commandInsertInvoice.ExecuteNonQuery();

                    // get gia tri column student_number  
                    OleDbCommand commandUpStuNum = new OleDbCommand();
                    commandUpStuNum.Connection = cnInsert;
                    commandUpStuNum.CommandText = "select * from class where id = @id ";
                    commandUpStuNum.Parameters.AddWithValue("@id", idclass);
                    OleDbDataAdapter adaptUpStuNum = new OleDbDataAdapter(commandUpStuNum);
                    DataTable dataUpStuNum = new DataTable();
                    adaptUpStuNum.Fill(dataUpStuNum);
                    DataRow drUpStuNum = dataUpStuNum.Rows[0];
                    int student_number = int.Parse(drUpStuNum["student_number"].ToString());
                    student_number++;
                    waiting_student++;
                    int limit_student = int.Parse(drUpStuNum["limit_student"].ToString());
                    int fullstu = int.Parse(drUpStuNum["full"].ToString()); ;
                    if (student_number == limit_student)
                    {
                        fullstu = 1;
                        waiting_student -= 30;
                        if (waiting_student < 0) waiting_student = 0;
                    }
                    
                    // up gia tri cot student_number lên 1 trong class
                    OleDbCommand cmdUpstu = new OleDbCommand();
                    cmdUpstu.Connection = cnInsert;
                    cmdUpstu.CommandText = "UPDATE class SET student_number = @student_number , [full] = @full WHERE id = @id";
                    cmdUpstu.Parameters.Add("@student_number", OleDbType.Integer).Value = student_number;
                    cmdUpstu.Parameters.Add("@full", OleDbType.Integer).Value = fullstu;
                    cmdUpstu.Parameters.Add("@id", OleDbType.Integer).Value = idclass;
//                  cmdUpstu.Parameters.Add("@STT", OleDbType.Integer).Value = Convert.ToInt32(txtSTT.Text);
                    cmdUpstu.ExecuteNonQuery();

                    // up gia tri cot waiting_student lên 1 trong course
                    OleDbCommand cmdUpWaitStu = new OleDbCommand();
                    cmdUpWaitStu.Connection = cnInsert;
                    cmdUpWaitStu.CommandText = "UPDATE course SET waiting_student = @waiting_student WHERE id = @id";
                    cmdUpWaitStu.Parameters.Add("@waiting_student", OleDbType.Integer).Value = waiting_student;
                    cmdUpWaitStu.Parameters.Add("@id", OleDbType.Integer).Value = idcourse;
                    cmdUpWaitStu.ExecuteNonQuery();

                    cnInsert.Close();
                    cnInsert.Dispose();
                    cnInsert = null;

                    MessageBox.Show("Đăng Ký Thành Công", "Thông Báo");
                    Close();

                }
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }

        private void BT_kiemtraHVadd_Click(object sender, EventArgs e)
        {
            try
            {
                OleDbConnection cncheck = new OleDbConnection();
                cncheck.ConnectionString = connection_string;
                cncheck.Open();
                OleDbCommand commandcheck = new OleDbCommand();
                commandcheck.Connection = cncheck;
                commandcheck.CommandText = "select * from student where fullname = @fullname";
                commandcheck.Parameters.AddWithValue("@fullname", TB_kiemtraHVadd.Text);
                OleDbDataAdapter adapt = new OleDbDataAdapter(commandcheck);
                DataTable data = new DataTable();
                adapt.Fill(data);
              
                if (data.Rows.Count > 0)
                {
                    MessageBox.Show("Bạn Đã Đăng Ký Tại Trung Tâm","Thông Báo");
                }
                else
                {
                    MessageBox.Show("Bạn Chưa Đăng Ký Tại Trung Tâm", "Thông Báo");
                }
                cncheck.Close();
                cncheck.Dispose();
                cncheck = null;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }

        
     

    }
}
